CREATE DATABASE [MINER]
GO

USE [MINER]

CREATE TABLE tblUsers
(
	ID INT NOT NULL IDENTITY(1, 1),
	[Login] nvarchar(32) NOT NULL,
	FirstName nvarchar(32) NULL,
	LastName nvarchar(32) NULL,
	PasswordHash nvarchar(32) NOT NULL
	
	CONSTRAINT PK_USERS PRIMARY KEY (ID),
	CONSTRAINT UK_USERS_LOGIN UNIQUE ([Login])
)

CREATE TABLE tblGames
(
	ID UNIQUEIDENTIFIER NOT NULL,
	[UserID] INT NOT NULL,
	[State] TINYINT NOT NULL,
	[BoardState] image NOT NULL,
	[StartTime] DATETIME NOT NULL,
	[EndTime] DATETIME NULL,
	XSize TINYINT NOT NULL,
	YSize TINYINT NOT NULL,
	MineCount TINYINT NOT NULL
	
	CONSTRAINT PK_GAMES PRIMARY KEY (ID),
	CONSTRAINT FK_GAMES_USER FOREIGN KEY (UserID) REFERENCES tblUsers(ID)
)

CREATE TABLE tblGameLog
(
	GameID UNIQUEIDENTIFIER NOT NULL,
	[Time] DATETIME NOT NULL,
	X TINYINT NOT NULL,
	Y TINYINT NOT NULL,
	ActionKind tinyint NOT NULL,
	
	CONSTRAINT FK_GAMELOG_GAME FOREIGN KEY (GameID) REFERENCES tblGames(ID)
)


INSERT INTO tblUsers ([Login], FirstName, LastName, PasswordHash)
VALUES ('lex', 'Volodymyr', 'Shtenovych', 'lex')